package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.*;

public class DbSupplier implements IFDBGeneric<Supplier> {

	private Connection con;

	public DbSupplier()
	{
		con = DbConnection.getInstance().getDBcon();
	}
	
	
	
	public ArrayList<Supplier> getAll() {
		return  miscWhere("");
	}

	
	public Supplier find(String name) {
		return singleWhere("name = '"+name+"'");
	}
	
	public Supplier find_byID(int id) {
		return singleWhere("sid = "+id);
	}
	
	
	public Supplier find_byCVR(String cvr) {
		return singleWhere("cvr = '"+cvr+"'");
	}
	
	

	public int delete(int sid)
	{
		int rc = -1;
		
		String query = "DELETE FROM People WHERE pid = "+sid;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't delete the supplier with id "+sid);
		}
		
		
		return rc;
	}
	
	
	public int insert(Supplier sup) throws Exception 
	{
		int rc = -1;
		
		String query1 = "INSERT INTO People (name,address,phoneNo,email) VALUES "
				+ "('"+sup.getName()+"','"+sup.getAddress()+"','"+sup.getPhoneNo()+"','"+sup.getEmail()+"')";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1, Statement.RETURN_GENERATED_KEYS);
			
			
			ResultSet rs = stmt.getGeneratedKeys();
	        if (rs.next())  rc = rs.getInt(1);
	        rs.close();
			
			if(rc > 0)
			{
				
				String query2 = "INSERT INTO Supplier (sid,companyName,cvr) VALUES "
					+ "("+rc+",'"+sup.getCompanyName()+"','"+sup.getCvr()+"')";
				
				rc = stmt.executeUpdate(query2);
			}
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't insert the supplier with name "+sup.getName());
		}
		
		
		return rc;
	}

	
	public int update(Supplier sup) 
	{
		int rc = -1;
		
		String query1 = "UPDATE People SET name = '"+sup.getName()+"',address = '"+sup.getAddress()+"',"
				+ "phoneNo = '"+sup.getPhoneNo()+"',email = '"+sup.getEmail()+"' WHERE pid = "+sup.getPid();
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query1);
			
			if(rc > 0)
			{
				String query2 = "UPDATE Supplier SET companyName = '"+sup.getCompanyName()+"',cvr = '"+sup.getCvr()+"' WHERE sid = "+sup.getPid();
				rc = stmt.executeUpdate(query2);
			}
			
			stmt.close();
		}
		catch(Exception ex){
			System.err.println("Couldn't update the supplier with name "+sup.getName());
		}
		
		
		return rc;
	}
	
	
	
	
	
	
	
	
	private Supplier singleWhere(String wClause)
	{
		Supplier supplier =null;
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
				supplier = buildSupplier(results);
			
			stmt.close();
		}
		catch(Exception ex){}
		
		return supplier;
	}
	
	
	
	private ArrayList<Supplier> miscWhere(String wClause)
	{
		ArrayList<Supplier> suppliers = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			
			while(results.next())
			{
				Supplier supplier = buildSupplier(results);
				suppliers.add(supplier);
			}
			
			stmt.close();
			
		}
		catch(Exception ex){
			System.err.println("Couldn't select multiple supplier tuples !");
		}
		
		
		
		
		return suppliers;
	}
	
	
	
	
	
	
	private Supplier buildSupplier(ResultSet result)
	{
		Supplier supplier = new Supplier();
		
		try{	
			supplier.setPid(result.getInt("sid"));
			supplier.setCompanyName(result.getString("companyName"));
			supplier.setCvr(result.getString("cvr"));
			supplier.setName(result.getString("name"));
			supplier.setAddress(result.getString("address"));
			supplier.setPhoneNo(result.getString("phoneNo"));
			supplier.setEmail(result.getString("email"));
		}
		catch(Exception ex){
			System.err.println("Couldn't build the customer !");
		}
		
		return supplier;
	}
	
	
	private String buildQuery(String wClause)
	{
		String query = "Select sid,companyName,cvr,name,address,phoneNo,email from Supplier,People where sid=pid";
		
		if(wClause.length() > 0) query = query + " AND " + wClause;
		
		return query;
	}


	
}
